package com.tim.helper.sql;

import cn.hutool.poi.excel.ExcelUtil;
import com.tim.util.DbUtils;
import com.tim.util.StringAnalysisUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;

import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @Description:
 * @Version 1.0, 2021/5/19
 * @Author Tim
 */
public class TableHelper {
    String url = "jdbc:mysql://rm-bp158x381519cc14qjo.mysql.rds.aliyuncs.com:3306/information_schema";
    String uname = "dev_admin";
    String pwd = "Dev+2019@lazypig";

    String filePath = "E:\\temp\\table\\table.xls";

    List<String> inList = Arrays.asList(

    		);
    String searchInfo = "menu";
    @Test
    public void searchTable() throws Exception {
        
        boolean noSearchInfo = StringUtils.isBlank(searchInfo);// false混合查询，true纯in查询
        searchInfo = noSearchInfo?"AAAAAAAAA":searchInfo;
        String inStr = inList.stream().map(item->"\""+item+"\"").collect(Collectors.joining(", ", "(", ")"));
        inStr = inStr.replace("()", "(\"\")");
        Connection conn = DbUtils.getConnection(url,uname,pwd);
        String query = String.format("select * from tables where TABLE_NAME like concat('%%', '%s', '%%') OR TABLE_NAME IN %s\n",
        		searchInfo, inStr);
//        query = "";
        System.out.println(query);
        ResultSet rs = conn.createStatement().executeQuery(query);
        int num = 1;
        List<String> tables = new ArrayList<>();
        Map<String, String> tableCommentMap = new HashMap<>();
        while(rs.next()){
            String tableName = rs.getString("TABLE_NAME");
            String className = StringAnalysisUtil.toPascal(tableName);
            String tableComment = rs.getString("TABLE_COMMENT");
            System.out.println(String.format("%sDO t%s;// %s，%s", className, num++, tableName, tableComment));
            tables.add(tableName);
            tableCommentMap.put(tableName, tableComment);
        }

        List<List<String>> contents = new ArrayList<>();
        contents.add(Arrays.asList("字段名", "字段code", "字段类型"));
        num=1;
        tables = noSearchInfo ? inList : tables;
        for(String table : tables){
            String queryCol = String.format("SELECT * FROM `COLUMNS` WHERE `TABLE_NAME` = \"%s\"", table);
            rs = conn.createStatement().executeQuery(queryCol);
            contents.add(Arrays.asList("t"+(num++)+"；"+tableCommentMap.get(table), table));
            while(rs.next()){
                String columnName = rs.getString("COLUMN_NAME");
                String columnType = rs.getString("COLUMN_TYPE");
                String columnComment = rs.getString("COLUMN_COMMENT");
                columnComment = StringUtils.isNotBlank(columnComment) ? columnComment : columnName;
                // 写xls
                contents.add(Arrays.asList(columnComment, columnName, columnType));
//                System.out.println(String.format("%s, %s, %s", columnName, columnType, columnComment));
            }

        }
//        contents.forEach(System.out::println);
        Files.deleteIfExists(Paths.get(filePath));
        ExcelUtil.getWriter(filePath, "Sheet1")
                .write(contents)
                .flush()
                .close();
    }

    @Test
    public void renameVar(){
        String code = "AcOperationCompanyInfoDO t6;// ac_operation_company_info，运营公司表\r\n" + 
        		"        AcCompanyAttach t1;// ac_company_attach，公司附件表\r\n" + 
        		"        AcOperationAreaInfo t5;// ac_operation_area_info，运营公司区域表\r\n" + 
        		"\r\n" + 
        		"        AcAuditRecord t2;// ac_audit_record，公司审核记录\r\n" + 
        		"        AcCompanyFinancial t3;// ac_company_financial，财务信息\r\n" + 
        		"        AcCompanyRatioSettingHistoryDo t4;// ac_company_ratio_setting_history，运营公司 - 管理费比率历史记录\r\n" + 
        		"        "
        		+ ""
        		+ "";
        int num = 1;
        Pattern pattern = Pattern.compile("( t\\d{1,2})");
        for(String str : code.split("\n")){
            if(StringUtils.isNotBlank(str)){
                Matcher matcher = pattern.matcher(str);
                if(matcher.find()){
                    String target = matcher.group();
                    str = str.replaceFirst(Pattern.quote(target), " t"+num++);
                }
            }
            System.out.print(str);
        }
    }

    @Test
    public void printTableName() throws Exception{
        String code = "AcOperationCompanyInfoDO t1;// ac_operation_company_info，运营公司表\r\n" + 
        		"        AcCompanyAttach t2;// ac_company_attach，公司附件表\r\n" + 
        		"        AcOperationAreaInfo t3;// ac_operation_area_info，运营公司区域表\r\n" + 
        		"\r\n" + 
        		"        AcAuditRecord t4;// ac_audit_record，公司审核记录\r\n" + 
        		"        AcCompanyFinancial t5;// ac_company_financial，财务信息\r\n" + 
        		"        AcCompanyRatioSettingHistoryDo t6;// ac_company_ratio_setting_history，运营公司 - 管理费比率历史记录\r\n" + 
        		""
        		+ "";
        boolean isFirst = true;
        inList = new ArrayList<>();
        searchInfo = "";
        for(String str : code.split("\n")){
            str = str.trim();
            int start = str.indexOf("//");
            if(start>5){
            	String pre = isFirst ? "" : ", ";
            	isFirst = false;
            	String tableName = str.substring(start+3, str.indexOf("，"));
                inList.add(tableName.trim());
                System.out.println(pre+"\""+tableName+"\"");
            }
        }
        // 生成powerDesigner导入文件
        if(CollectionUtils.isNotEmpty(inList)){
            searchTable();
        }
    }


}
